﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtility;

namespace SQLServerDAL.HR
{
    public class JobsBase
    {
        public JobsBase() { }
  
        public string Add(Model.HR.JobsBase __news)
        {
            string result = "";

            string _jobId = System.Guid.NewGuid().ToString();
            string sqlstring = "INSERT INTO JobsBase([JobId],[JobTitle],[WorkAddress],[PeopleNum],[EducationAbility],[ProfessionalAbility],[ComputerAbility],[EnglishAbility],[ExperienceReq],[SexReq],[AgeReq],[OthersReq],[HomePlaceReq],[LineNum],[CreatedOn],[CreatedBy],[CategoryId],[WorkAddressCode],[WorkAddressTitle],[JobType],[JobTypeCode],[PublishDate],[DutyDescription])";
            sqlstring += " Values(@JobId,@JobTitle,@WorkAddress,@PeopleNum,@EducationAbility,@ProfessionalAbility,@ComputerAbility,@EnglishAbility,@ExperienceReq,@SexReq,@AgeReq,@OthersReq,@HomePlaceReq,@LineNum,@CreatedOn,@CreatedBy,@CategoryId,@WorkAddressCode,@WorkAddressTitle,@JobType,@JobTypeCode,@PublishDate,@DutyDescription)";
            SqlParameter[] prams = {
						SqlHelper.CreateInputParam("@JobId", SqlDbType.UniqueIdentifier, new Guid(_jobId)),
						SqlHelper.CreateInputParam("@JobTitle", SqlDbType.VarChar, __news.JobTitle),
						SqlHelper.CreateInputParam("@WorkAddress", SqlDbType.VarChar, __news.WorkAddress),
						SqlHelper.CreateInputParam("@PeopleNum", SqlDbType.VarChar, __news.PeopleNum),
						SqlHelper.CreateInputParam("@EducationAbility", SqlDbType.VarChar, __news.EducationAbility),
						SqlHelper.CreateInputParam("@ProfessionalAbility", SqlDbType.VarChar, __news.ProfessionalAbility),
						SqlHelper.CreateInputParam("@ComputerAbility", SqlDbType.VarChar, __news.ComputerAbility),
                        SqlHelper.CreateInputParam("@EnglishAbility", SqlDbType.VarChar, __news.EnglishAbility),
						SqlHelper.CreateInputParam("@ExperienceReq", SqlDbType.VarChar, __news.ExperienceReq),
						SqlHelper.CreateInputParam("@SexReq", SqlDbType.VarChar, __news.SexReq),
                        SqlHelper.CreateInputParam("@AgeReq", SqlDbType.VarChar, __news.AgeReq),
                        SqlHelper.CreateInputParam("@OthersReq", SqlDbType.VarChar, __news.OthersReq),
                        SqlHelper.CreateInputParam("@HomePlaceReq", SqlDbType.VarChar, __news.HomePlaceReq),
                        SqlHelper.CreateInputParam("@LineNum", SqlDbType.Decimal, __news.LineNum),
                        SqlHelper.CreateInputParam("@CreatedOn", SqlDbType.DateTime, DateTime.Now),
                        SqlHelper.CreateInputParam("@CreatedBy",SqlDbType.UniqueIdentifier, new Guid(__news.CreatedBy)),
                        SqlHelper.CreateInputParam("@CategoryId", SqlDbType.UniqueIdentifier, new Guid(__news.CategoryId)),
                        SqlHelper.CreateInputParam("@WorkAddressCode", SqlDbType.VarChar, __news.WorkAddressCode),
                        SqlHelper.CreateInputParam("@WorkAddressTitle", SqlDbType.VarChar, __news.WorkAddressTitle),
                        SqlHelper.CreateInputParam("@PublishDate", SqlDbType.DateTime, __news.PublishDate),
                        SqlHelper.CreateInputParam("@JobType", SqlDbType.VarChar, __news.JobType),
                        SqlHelper.CreateInputParam("@JobTypeCode", SqlDbType.VarChar, __news.JobTypeCode),
                        SqlHelper.CreateInputParam("@DutyDescription", SqlDbType.VarChar, __news.DutyDescription)
			};

            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlstring, prams);
                result = "succeeded|" + _jobId;
            }
            catch (Exception ex)
            {
                result = ex.ToString();
            }

            return result;
        }

        public string Update(Model.HR.JobsBase __news)
        {
            string result = "";

            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    string sqlstring = "UPDATE [JobsBase] SET [JobTitle] = @JobTitle,[WorkAddress] = @WorkAddress,[PeopleNum] = @PeopleNum,[EducationAbility] = @EducationAbility,[ProfessionalAbility] = @ProfessionalAbility,[ComputerAbility] = @ComputerAbility,[EnglishAbility] = @EnglishAbility,[ExperienceReq] = @ExperienceReq,[SexReq] = @SexReq,[AgeReq] = @AgeReq,[OthersReq] = @OthersReq,[HomePlaceReq] = @HomePlaceReq,[LineNum] = @LineNum,[CategoryId] = @CategoryId,[WorkAddressCode] = @WorkAddressCode,[WorkAddressTitle] = @WorkAddressTitle,[JobType] = @JobType, [JobTypeCode] = @JobTypeCode,[PublishDate] = @PublishDate,DutyDescription=@DutyDescription Where JobId = @JobId";
                    SqlParameter[] prams = {
						SqlHelper.CreateInputParam("@JobId", SqlDbType.UniqueIdentifier, new Guid(__news.JobId)),
						SqlHelper.CreateInputParam("@JobTitle", SqlDbType.VarChar, __news.JobTitle),
						SqlHelper.CreateInputParam("@WorkAddress", SqlDbType.VarChar, __news.WorkAddress),
						SqlHelper.CreateInputParam("@PeopleNum", SqlDbType.VarChar, __news.PeopleNum),
						SqlHelper.CreateInputParam("@EducationAbility", SqlDbType.VarChar, __news.EducationAbility),
						SqlHelper.CreateInputParam("@ProfessionalAbility", SqlDbType.VarChar, __news.ProfessionalAbility),
						SqlHelper.CreateInputParam("@ComputerAbility", SqlDbType.VarChar, __news.ComputerAbility),
                        SqlHelper.CreateInputParam("@EnglishAbility", SqlDbType.VarChar, __news.EnglishAbility),
						SqlHelper.CreateInputParam("@ExperienceReq", SqlDbType.VarChar, __news.ExperienceReq),
						SqlHelper.CreateInputParam("@SexReq", SqlDbType.VarChar, __news.SexReq),
                        SqlHelper.CreateInputParam("@AgeReq", SqlDbType.VarChar, __news.AgeReq),
                        SqlHelper.CreateInputParam("@OthersReq", SqlDbType.VarChar, __news.OthersReq),
                        SqlHelper.CreateInputParam("@HomePlaceReq", SqlDbType.VarChar, __news.HomePlaceReq),
                        SqlHelper.CreateInputParam("@LineNum", SqlDbType.Decimal, __news.LineNum),
                        SqlHelper.CreateInputParam("@CategoryId", SqlDbType.UniqueIdentifier, new Guid(__news.CategoryId)),
                        SqlHelper.CreateInputParam("@WorkAddressCode", SqlDbType.VarChar, __news.WorkAddressCode),
                        SqlHelper.CreateInputParam("@WorkAddressTitle", SqlDbType.VarChar, __news.WorkAddressTitle),
                        SqlHelper.CreateInputParam("@PublishDate", SqlDbType.DateTime, __news.PublishDate),
                        SqlHelper.CreateInputParam("@JobType", SqlDbType.VarChar, __news.JobType),
                        SqlHelper.CreateInputParam("@JobTypeCode", SqlDbType.VarChar, __news.JobTypeCode),
                        SqlHelper.CreateInputParam("@DutyDescription", SqlDbType.VarChar, __news.DutyDescription)
					};
 
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlstring, prams);

                    trans.Commit();
                    result = "succeeded|" + __news.JobId;
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                    trans.Rollback();
                    throw ex;
                }
            }
            conn.Close();

            return result;
        }

        public Model.HR.JobsBase GetObjById(string newsid)
        {
            Model.HR.JobsBase __obj = null;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM vw_JobsBase WHERE [JobId] = @JobId;");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@JobId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(newsid);

            DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                __obj = new Model.HR.JobsBase();
                __obj.CategoryId = dr["CategoryId"].ToString();
                __obj.CategoryNumber = dr["CategoryNumber"].ToString(); 
                __obj.JobId = dr["JobId"].ToString();
                __obj.JobTitle = dr["JobTitle"].ToString();
                __obj.WorkAddress = dr["WorkAddress"].ToString();
                __obj.PeopleNum = dr["PeopleNum"].ToString();
                __obj.EducationAbility = dr["EducationAbility"].ToString();
                __obj.ProfessionalAbility = dr["ProfessionalAbility"].ToString();
                __obj.ComputerAbility = dr["ComputerAbility"].ToString();
                __obj.EnglishAbility = dr["EnglishAbility"].ToString();
                __obj.ExperienceReq = dr["ExperienceReq"].ToString();
                __obj.SexReq = dr["SexReq"].ToString();
                __obj.AgeReq = dr["AgeReq"].ToString();
                __obj.OthersReq = dr["OthersReq"].ToString();
                __obj.HomePlaceReq = dr["HomePlaceReq"].ToString(); 
                __obj.LineNum = decimal.Parse(dr["LineNum"].ToString());
                __obj.CreatedOn = DateTime.Parse(dr["CreatedOn"].ToString());
                __obj.CreatedBy = dr["CreatedBy"].ToString(); 
                __obj.WorkAddressCode = dr["WorkAddressCode"].ToString();
                __obj.WorkAddressTitle = dr["WorkAddressTitle"].ToString();
                __obj.PublishDate = DateTime.Parse(dr["PublishDate"].ToString());
                __obj.JobType = dr["JobType"].ToString();
                __obj.JobTypeCode = dr["JobTypeCode"].ToString();
                __obj.DutyDescription = dr["DutyDescription"].ToString();
                 
                dt.Dispose();
            }

            return __obj;
        } 

        public void Delete(string newsid)
        {
            StringBuilder strSql = new StringBuilder();
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@JobId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(newsid);

            strSql.Append("DELETE FROM JobsBase WHERE [JobId] = @JobId;");

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
        }
    }
}
